Excel funkcije INDEX i MATCH

Otkrijte potencijal moćne kombinacije za pretraživanje

INDEX i MATCH su Excel funkcije pretraživanja. Iako su dve potpuno odvojene funkcije koje se mogu koristiti samostalno, one se takođe mogu kombinovati za kreiranje naprednih formula.

Funkcija INDEX vraća vrednost ili referencu na vrednost iz određenog izbora. Na primer, mogla bi se koristiti za pronalaženje vrednosti u drugom redu skupa podataka ili u petom redu i trećoj koloni.

Iako se funkcija INDEX vrlo dobro može koristiti sama, unošenje MATCH-a u formulu čini je malo korisnijom. Funkcija MATCH traži određenu stavku u rasponu ćelija, a zatim vraća relativni položaj stavke u rasponu. Na primer, mogla bi se koristiti za utvrđivanje da li je određeno ime treća stavka na listi imena.

NAPOMENA: Funkcije INDEX i MATCH mogu se koristiti u svim verzijama Excela.

INDEX i MATCH sintaksa i argumenti

Ovako treba pisati obe funkcije kako bi ih Excel mogao razumeti:

=INDEX(array, row_num, [column_num])

• array je raspon ćelija koje će formula koristiti. To može biti jedan ili više redova i kolona, kao što je A1:D5. Obavezan argument.
• row_num je red u nizu iz kojeg treba vratiti vrednost, kao što je 2 ili 18. Obavezan je argument ako nije prisutan column_num.
• column_num je kolona u nizu iz koje se vraća vrednost, kao što je 1 ili 9. Opcioni argument.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value je vrednost koju želite da se podudara u lookup_value. To može biti broj, tekst ili logička vrednost koja se upisuje ručno ili se upućuje preko reference ćelije. Obavezan argument.
• lookup_array je raspon ćelija koje treba da se pregledaju. To može biti jedan red ili jedna kolona, poput A2:D2 ili G1:G45. Obavezan argument.
• match_type može biti -1, 0, ili 1. Određuje kako se lookup_value podudara sa vrednostima u lookup_array (vidi dole). 1 je podrazumevana vrednost ako je ovaj argument izostavljen.

Match_type	Šta radi	Pravila	Primer
1	Pronalazi najveću vrednost koja je manja ili jednaka lookup_value.	Vrednosti lookup_array moraju biti smeštene u uzlaznom redosledu (na primer, -2, -1, 0, 1, 2; ili A-Z; ili FALSE, TRUE.	lookup_value je 25, ali nedostaje lookup_array, pa se umesto njega vraća pozicija sledećeg najmanjeg broja, kao što je 22.
0	Pronalazi prvu vrednost koja je tačno jednaka lookup_value.	Vrednosti lookup_array mogu biti u bilo kojem redosledu.	lookup_value je 25, pa se vraća pozicija broja 25.
-1	Pronalazi najmanju vrednost koja je veća ili jednaka lookup_value.	Vrednosti lookup_array moraju biti postavljene u silaznom redosledu (na primer, 2, 1, 0, -1, -2).	lookup_value je 25, ali nedostaje lookup_array, pa se umesto njega vraća pozicija sledećeg najvećeg broja, kao što je 34.

Koristite 1 ili -1 za slučajeve kada treba da pokrenete približni pregled duž selekcije, kao na primer kada se bavite brojevima i kada su aproksimacije u redu. Ali zapamtite da ako ne navedete match_tipe, 1 će biti podrazumevano, što može pokvariti rezultate ako zaista želite tačno podudaranje.

Primeri formule INDEKS i MATCH

Pre nego što pogledamo kako se kombinuju INDEKS i MATCH u jednoj formuli, treba da razumemo kako ove pojedinačne funkcije rade.

Primeri funkcije INDEX

=INDEX(A1:B2,2,2)
=INDEX(A1:B1,1)
=INDEX(2:2,1)
=INDEX(B1:B2,1)

U ovom primeru, prikazane su četiri INDEKS formule koje možemo da koristimo da dobijemo različite vrednosti:

• =INDEX(A1:B2,2,2) pretražuje opseg A1:B2 da pronađe vrednost u drugoj koloni i drugom red, a to je Staci.
• =INDEX(A1:B1,1) pretražuje opseg A1:B1 da pronađe vrednost u prvoj koloni, a to je Jon.
• =INDEX(2:2,1) pretražuje sve u drugom redu da pronađe vrednost u prvoj koloni, a to je Tim.
• =INDEX(B1:B2,1) pretražuje opseg B1:B2 da pronađe vrednost u prvoj koloni, a to je Amy.

Primeri funkcije MATCH

=MATCH("Stacy",A2:D2,0)
=MATCH(14,D1:D2)
=MATCH(14,D1:D2,-1)
=MATCH(13,A1:D1,0)

U ovom primeru, prikazane su četiri MATCH formule:

• =MATCH(“Stacy”,A2:D2,0) traži string Stacy u opsegu A2:D2 i vraća 3 kao rezultat.
• =MATCH(14,D1:D2) traži broj 14 u opsegu D1:D2, ali pošto nije pronađeno u tabeli, MATCH pronalazi slefeću najveću vrednost koja je manja od 14, što je u ovom slučaju 13, što je na poziciji 1 u lookup_array.
• =MATCH(14,D1:D2,-1) is identical to the formula above it, but since the array isn’t in descending order like -1 requires, we get an error.
• =MATCH(13,A1:D1,0) traži broj 13 u prvom redu radnog lista, što vraća 4 jer je to četvrta stavka u ovom nizu.

Primeri kombinacije INDEX-MATCH funkcija

Ovde su prikazana dva primera kako možemo da kombinujemo INDEX i MATCH u jednoj formuli:

Pronađite referencu ćelije u tabeli

=INDEX(B2:B5,MATCH(F1,A2:A5))

U ovom primeru se MATCH formula se ubacuje unutar INDEX formule. Cilj je da se identifikuje boja koristeći broj (#).

Ako pogledate sliku, možete videti u redovima “Separated” šta daje svaka pojedinačna formula ali, pošto smo ih kombinovali, ovo je ono što ćemo dobiti kao rezultat:

• MATCH(F1,A2:A5) traži vrednost ćelije F1 (8795) u opsegu A2:A5. Ako pogledamo kolonu A na dole, videćemo da se tražena vrednost nalazi na poziciji 2, što je funkcija MATCH i dala kao rezultat.
• INDEX niz je B2:B5 jer smo u potrazi za vrednost u toj koloni.
• INDEX funkcija sada može biti napisana ovako pošto je 2 ono što je funkcija MATCH pronašla: INDEX(B2:B5, 2, [column_num]).
• Kako column_num nije obavezan atribut, možemo ga izostaviti tako da ostaje: INDEX(B2:B5,2).
• Sada ovo izgleda kao normalna INDEX formula gde nalazimo vrednost druge stavke niza B2:B5, a to je red.

Pretraživanje po zaglavljima redova i kolona

=INDEX(B2:E13,MATCH(H1,A2:A13,0),MATCH(H2,B1:E1,0))

U ovom MATCH and INDEX primeru, radićemo dvosmernu pretragu. Ideja je da se vidi koliko novca smo zaradili od Green stavki u mesecu May. Ovo je slično primeru iznad, ali se dodatna MATCH formula ubacuje u INDEX.

• MATCH(G1,A2:A13,0) je prva stavka koja se rešava u ovoj formuli. Ona traživrednost ćelije G1 (string “May”) u nizu A2:A13 da bi dobila određenu vrednost. Mi to ovde ne vidimo, ali to je 5.
• MATCH(H2,B1:E1,0) je druga MATCH formula, i slična je prvoj traži vrednost ćelije H2 (string “Green”) u zaglavljima kolona B1:E1. U našem primeru dobijamo vrednost 3.
• Sada možemo ponovo napisati INDEX formulu na sledeći način da prikažemo šta se dešava: =INDEX(B2:E13,5,3). Ova formula traži u celoj tabeli, B2:E13, peti red i treću kolonu, i kao rezultat daje vrednost $180.

Pravila MATCH i INDEX

Postoji nekoliko stvari koje treba imati u vidu kada se pišu formule sa ovim funkcijama :

• MATCH nije case sensitive, tako da se velika i mala slova tretiraju isto kod tekstualnih vrednosti.
• MATCH vraća #N/A iz više razloga: ako je match_type 0 i lookup_value nije pronađena, ako match_type je -1 i lookup_array nije u opadajućem poretku, ako match_type je 1 i lookup_array nije u rastućem poretku i ako lookup_array nije jedan red ili kolina.
• Možete koristiti i džoker znak kao lookup_value argument ako match_type je 0 i lookup_value je text string. Upitnik zamenjuje bilo koji karakter i zvezdica zamenjuje bilo koji niz od znakova (na primer, =MATCH(“Jo*”,1:1,0)). Da biste koristili MATCH da biste našli bilo koji upitnik ili zvezdicu, prvo otkucajte tildu (~).
• INDEX vraća #REF! ako row_num i column_num ne ukazuju na ćeliju u nizu.

Srodne Excel Funkcije

MATCH funkcija je slična LOOKUP funkciji, ali MATCH vraća poziciju stavke umesto samu stavku.

VLOOKUP je još jedna funkcija za pretraživanje koju možete da koristite u Excel-u, ali za razliku od MATCH i INDEX za naprednu pretragu, VLOOKUP formula zahteva samo tu jednu formulu.